dBASE III Bugs by LANA MOUNTFORD The first really troublesome bug deals with the INSERT command. If you INSERT a record into the middle of a file, the last (physical) record in the file is deleted. However, if you then issue any command which shows # of records in the file (e.g., DISPLAY STRUCTURE), the "new correct" number is displayed. Example: File contains 36 records; INSERT a new record after #21; DISPLAY STRUCTURE will show # of records as 37, but if you LIST ALL or DISPLAY ALL, only 36 records will appear. The "original" 36th record will be missing. Ashton-Tate has a fix for this one in the works, but in the meantime, you can get around it two ways. If the order of the records isn't important, just use APPEND, which places the new record at the end of the file. If order is critical, you'll need to create a new work file, APPEND from the "real" file all the records you want to appear before the new record; then APPEND the new record, and finally APPEND the rest of the records from the "real" file. Now, you have two files. At this point, go into DOS, delete the original "real" file, and rename the new file. Of course, all of this is moot if the file is indexed -- APPEND will automatically place the record in its proper (logical) position. This one involves one of the new field types -- the MEMO field. When you JOIN a file which contains a MEMO field with a file which does not contain a MEMO field, any attempt to USE the resulting file will cause you to bomb, with the message "File does not exist -- Memo file cannot be opened." Even if FIELDS was specified, and the Memo field was specifically excluded, you will still get this error. Example: SELECT 1 USE ALIAS S1 SELECT 2 USE ALIAS S2 JOIN WITH S1 TO TEMPFILE FOR USE TEMPFILE At this point the nasty message appears. Actually, the file does exist, but you can't get to it. It seems dBASE uses the structure of the active file (in this case, the file in area 2 [ALIAS S2] is the active file) to establish the structure of the target file, taking into account any FIELDS specified. However, it also seems to carry over the fact that the active file contains a pointer to a MEMO file (.DBT), but does nothing about copying/not copying over the actual memo records. The work-around is pretty obvious -- always join the file WITHOUT the MEMO field with the file with the MEMO field. In the above example, after definition of the two areas (after the second USE statement), you can insert: SELECT 1 then, change the JOIN statement: JOIN WITH S2 TO TEMPFILE FOR This causes the file WITHOUT the MEMO field to become the active file, and the resulting file will work with no problems. We had some interesting times with the new PICTURE feature. I was trying to edit input using a PICTURE of '@R 9/A99' which, according to the manual should result in a 5-character field, the first character of which must be numeric, the second character a slash (/), the third character must be alpha, and the last two characters must be numbers. When this was used with GET, however, only characters were allowed in ANY position -- it would not allow numbers to be entered at all! Concerning speed -- did you follow the advice in the dBASE III manual to set BUFFERS = 24 in your CONFIG.SYS file? If so, try lowering it 5 or so. According to Ashton-Tate, someone came up with the 24, but their tests show optimum performance at around 15 or so. In any case, speed improved by about 40%! EXACT ON doesn't work. DON'T USE IT! We found this one when duplicate records started cropping up in our production master file. For those unfamiliar with how EXACT works, it is something like this: in dBASE, some fields compare as equal when they aren't REALLY equal. Example: 'ABC' is equal to 'ABCDEFGH'. If you want this situation, you SET EXACT ON to force the match to be an exact character-for- character comparison. In the above case, 'ABC' not equal 'ABCDEFGH'. In our application, it is critical that there be no duplicates on the master file, so of course we used SET EXACT ON. In the data entry routine, the user enters the identification code. We then do a FIND on the master file, and if we have a hit, the user is notified of the duplicate situation. If a FIND condition occurs, the user is allowed to truck on -- entering the rest of the initial data. In our case, we were getting random FINDs when in fact the duplicates existed. Took us over four hours track down all of the double entries and correct the file. This really falls into the category of idiosyncrasy, and REALLY poor documentation. It concerns the new DATE field category, and the features in the PICTURE clause. Basically, the following is undocumented but true. If a character variable contains the value '00/00/00' and is then converted to a date-formatted field via CTOD function, the resulting field value will be '11/30/99'. If the character variable is initialized to '99/99/99', then converted via CTOD, the result will be '06/23/07'. However, if the variable is initialized to '/ / ', then converted, the result is ' / / '! Also, one of the new PICTURE features allows you to edit for valid date by using PICTURE '@D'. According to the dBASEIII manual, this restricts entry via a GET to valid MO/DA/YR. What the book doesn't tell you is that the field MUST be defined as a date field. When dealing with memory variables, you can really only declare them three ways: Character (as in STORE 'abcde' to M_VAR) Numeric (as in STORE 19.75 to M_VAR) Logical (as in STORE .T. to M_VAR) unless you initialize them via STORE from a database or with a function command acting on an already-defined variable. Then: STORE ' / / ' TO M_VAR STORE CTOD(M_VAR) TO M_VAR @ x,y GET M_VAR PICTURE '@D' READ This creates the variable as character, converts it to date format, then restricts the entry of M_VAR to valid dates only. The error message appears at the top of the screen in line 0, and goes away upon re-entry or spaces, which dBASE seems to treat as a valid date. I know of no way to declare one DIRECTLY as a date field. As far as I know, you HAVE to declare it as character first, then do the function to get it into what dBASE recognizes as date format. You should not try to store a memory variable defined as a character in a database field defined as DATE. Example: REPLACE EFF_DATE WITH M_DATE, where M_DATE is character and EFF_DATE is DATE. dBASE is REAL picky about things like that, resulting in the error message: DATA TYPE MISMATCH TERMINATE COMMAND FILE (Y/N)? I want to preface this with some basic facts: 1) ALL of these have been reported to A-T in one form or another and confirmed by them. 2) I am NOT out to 'get' A-T. 3) There are apparently two (and possibly even three) different versions of version 1.0 of dBASE III. I am working with the EARLIEST version of version 1.0 which is where all of these bugs were encountered. I have the second version of version 1.0, and have just started testing our system with it. I have encountered at least one new bug (again verified by A-T) in this version. Some of what I am reporting here MAY NOT appear in these later version(s). 4) The work-arounds provided are in most cases my own, since this does not appear to be A-T's strong suit. That does not mean that the work-arounds are necessarily the best ones, just ones that I have come up with that work for me. If others have found better ones, PLEASE say so! That is one of the reasons I started this whole thing in the first place. While the the User Manual for dBASE III is a definite improvement over the one supplied for dBASE II, it still has some shortcomings. The index is screwed up. Some items don't have page numbers, quite a few numbers are wrong (topic doesn't appear on the referenced page), and some page numbers are non-existent. For example, look up "field - definition, 3-1,7-3". Not only does field names definition not appear on page 3-1 (it's actually on pg 3-2), but there is no page 7 in the entire manual. There are also more typos that one I would expect to find in a package of this price. I was told of another idiosyncrasy dealing with many of the filter or dual-positioning SET commands (e.g., SET COLOR TO, SET RELATION TO, SET DELETED, etc.). When using one of these, it may appear at first that the command isn't working. If the command is issued after a USE (and most of them have to be issued with at least one file in use), it will NOT apply to the current record. Example: You are currently USEing a file and are pointed at a record marked for deletion. You now SET DELETED to "hide" all records marked for deletion. If you now DISPLAY the current record, you will see the current record even though it is marked for deletion. In order for the command to take effect, you must cause some movement in the file. I usually follow the command with GO TOP, or if the command is issued immediately after the USE, A-T says to issue GO BOTTOM followed by GO TOP so that the first record is considered. The next problem was particularly sticky because A-T was never able to locate it. The work-around is even stranger. I noticed a problem with cursor positioning on entry of decimal numbers. The problem occurred whenever decimal input was attempted (in a READ of a numeric field) after access of any .DBF file USEd with INDEX. In these situations, as soon as the decimal point was entered, the cursor was immediately positioned at the beginning of the entry, rather than at the first decimal position. It didn't matter whether PICTURE was used or not; the cursor would ALWAYS pop back to the first position of the field, rather than the first position after the decimal point. We ran a number of tests and found that if the READ was preceded by ANY access (SEEK, FIND, LOCATE) of a file USEd with an INDEX, then the problem appeared. Further, if multiple files were in USE, if the active file had been last USEd with an index and accessed, the problem occurred. The problem did not appear with files USEd without indexes. The work- around? Start lowering the # of BUFFERS in your CONFIG.SYS file. (If you haven't already, it should be 15 or so for maximum efficiency). We had already lowered ours 15, so we tried lowering it to 10, and the problem disappeared. One other user, according to A-T, caused the problem to disappear by going from BUFFERS = 24 to BUFFERS = 17.